package myandroid.net.DAO;

import java.util.Vector;

import myandroid.net.DTO.DTO_Project;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import java.sql.Time;

public class DAO_Project extends SQLiteOpenHelper{

	private SQLiteDatabase gDatabase;
	
//	private static final String gProID = "ProID";
//	
//	private static final String gProName = "ProName";
//	
//	private static final String gProDescription = "ProDescription";
//	
//	private static final String gProStartTime = "12/12/2012";
//	
//	private static final String gProDeadline = "21/12/2012";
//	
//	private static final String gProStatus = "ProStatus";
//	
//	private static final String gProAvatar = "ProAvatar";
	
	private static final String DATABASE_CREATE = "CREATE TABLE [PROJECT] (" +
			"[ProID] VARCHAR(10)  PRIMARY KEY NOT NULL," +
			"[ProName] VARCHAR(100)  NOT NULL," +
			"[ProDescription] NVARCHAR(300)  NULL," +
			"[ProStartTime] TIME  NOT NULL," +
			"[ProDeadline] TIME  NOT NULL," +
			"[ProStatus] VARCHAR(50)  NOT NULL," +
			"[ProAvatar] VARCHAR(50)  NOT NULL)";
	
	//private static final String DATABASE_NAME ="TIM_SEPRO";
	
	private static final String TABLE_NAME = "PROJECT";
	
	//private static final int DATABASE_VERSION = 1;
	
	public DAO_Project(Context context, String DATABASE_NAME, CursorFactory factory,
			int DATABASE_VERSION) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		
		gDatabase = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, factory);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(DATABASE_CREATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(DAO_Project.class.getName(), "Upgrading database from version " + oldVersion + "to" 
					+ newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS" + TABLE_NAME);
		onCreate(db);	
	}
	
	public void openWriteDatabase() throws SQLException{
		gDatabase = getWritableDatabase();
	}
	
	public void openReadDatabase() throws SQLException{
		gDatabase = getReadableDatabase();
	}
	
	public void closeDatabase() {
		gDatabase.close();
	}
	
	//them mot project moi vao trong database
	public void addProject(DTO_Project mProject){
		
		this.openWriteDatabase();
		
		String sql = "INSERT INTO " + TABLE_NAME + "(ProID,ProName, ProDescription,ProStartTime,ProDealine,ProStatus, " +
				"ProAvatar) VALUES(";
		
		sql += "'" + mProject.getgProID().trim() + "',";
		sql += "'" + mProject.getgProName() +"',";
		sql += "'" + mProject.getgProDescription() + "',";
		sql += mProject.getgProStartTime().toString() + ",";
		sql += mProject.getgProDeadline().toString() + ",'";
		sql += "'" + mProject.getgProStatus().trim() + "',";
		sql += "'" + mProject.getgProAvatar().trim() + "')";
		
		gDatabase.execSQL(sql);
		
//		ContentValues ctValues = new ContentValues();
//		
//		ctValues.put(gProID, mProject.getgProID());
//		
//		ctValues.put(gProName, mProject.getgProName());
//		
//		ctValues.put(gProDescription, mProject.getgProDescription());
//		
//		ctValues.put(gProStartTime, mProject.getgProStartTime().toString());
//		
//		ctValues.put(gProDeadline, mProject.getgProDeadline().toString());
//		
//		ctValues.put(gProStatus, mProject.getgProStatus());
//		
//		ctValues.put(gProAvatar, mProject.getgProAvatar());
//		
//		gDatabase.insert(TABLE_NAME, null, ctValues);
		
//		ctValues.clear();
		
		this.closeDatabase();
	}
	
	//update mot project trong database
	public void updateProject(DTO_Project mProject){
		
		this.openWriteDatabase();
		
		String sql = "UPDATE " + TABLE_NAME +" SET ";
		
		sql += "ProName = '" + mProject.getgProName().trim() + "',";
		sql += "ProDescription = '" + mProject.getgProDescription().trim() + "',";
		sql += "ProStartTime = " +	mProject.getgProStartTime().toString() + ",";
		sql += "ProDealine = " + mProject.getgProDeadline().toString() + ",";
		sql += "ProStatus = '" + mProject.getgProStatus().trim() + "',";
		sql += "ProAvatar = '" + mProject.getgProAvatar().trim() + "' ";
		sql += "WHERE ProID = '" + mProject.getgProID().trim() + "'";
		
		gDatabase.execSQL(sql);
		
//		ContentValues ctValues = new ContentValues();
//		
//		ctValues.put(gProName, mProject.getgProName());
//		
//		ctValues.put(gProName, mProject.getgProName());
//		
//		ctValues.put(gProDescription, mProject.getgProDescription());
//		
//		ctValues.put(gProStartTime, mProject.getgProStartTime().toString());
//		
//		ctValues.put(gProDeadline, mProject.getgProDeadline().toString());
//		
//		ctValues.put(gProStatus, mProject.getgProStatus());
//		
//		ctValues.put(gProAvatar, mProject.getgProAvatar());
//		
//		gDatabase.update(TABLE_NAME, ctValues, gProID + "=?",
//				new String[]{String.valueOf(mProject.getgProID())});
		
		this.closeDatabase();
	}
	
	
	//xoa mot project trong database theo ma id
	public void deleteProject(String mProjectID){
		
		this.openWriteDatabase();
		
		String sql = "DELETE FROM " + TABLE_NAME + " ";
		sql += "WHERE ProID = '" + mProjectID.trim() + "'";
		gDatabase.execSQL(sql);
		
//		gDatabase.delete(TABLE_NAME, gProID + "=?", new String[]{String.valueOf(mProID)});
		
		this.closeDatabase();
	}
	
	//tim kiem cac project ki tu chua trong ten
	public Vector<DTO_Project> searchProName(String mProName) {
		
		this.openReadDatabase();
		
		String sql = "SELECT * FROM " + TABLE_NAME + " ";
		sql += "WHERE ProName LIKE '%" + mProName.trim() + "%'";
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		Vector<DTO_Project> list = new Vector<DTO_Project>();
		
		DTO_Project mProject = new DTO_Project();
		
		this.closeDatabase();
		
		if(mCursor.moveToFirst())
		{
			do
			{
				mProject.setgProID(mCursor.getString(0));
				mProject.setgProName(mCursor.getString(1));
				mProject.setgProDescription(mCursor.getString(2));
				mProject.setgProStartTime(new Time(mCursor.getLong(3)));
				mProject.setgProDeadline(new Time(mCursor.getLong(4)));
				mProject.setgProStatus(mCursor.getString(5));
				mProject.setgProAvatar(mCursor.getString(6));
				
				list.add(mProject);
				
			}while(mCursor.moveToNext());
			
			this.closeDatabase();
			
			return list;
			
		}
		else {
			
			this.closeDatabase();
			
			return null;
		}
	}
	
	//tim kiem cac project theo ki tu chua trong mo ta cua project.
	public Vector<DTO_Project> searchProDescrip(String mProDescrip) {
		
		this.openReadDatabase();
		
		String sql = "SELECT * FROM " + TABLE_NAME + " ";
		sql += "WHERE ProDescription LIKE '%" + mProDescrip.trim() + "%'";
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		Vector<DTO_Project> list = new Vector<DTO_Project>();
		
		DTO_Project mProject = new DTO_Project();
		
		this.closeDatabase();
		
		if(mCursor.moveToFirst())
		{
			do
			{
				mProject.setgProID(mCursor.getString(0));
				mProject.setgProName(mCursor.getString(1));
				mProject.setgProDescription(mCursor.getString(2));
				mProject.setgProStartTime(new Time(mCursor.getLong(3)));
				mProject.setgProDeadline(new Time(mCursor.getLong(4)));
				mProject.setgProStatus(mCursor.getString(5));
				mProject.setgProAvatar(mCursor.getString(6));
				
				list.add(mProject);
				
			}while(mCursor.moveToNext());
			
			this.closeDatabase();
			
			return list;
			
		}
		else {
			
			this.closeDatabase();
			
			return null;
		}
	}
	
	//lay thong tin ve mot project trong database theo ma id
	public DTO_Project getProject(String mProjectID) {
		
		this.openReadDatabase();
		
		String sql = "SELECT * FROM " + TABLE_NAME + " " +
				"WHERE ProID = '" + mProjectID.trim() + "'";
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		DTO_Project mProject = new DTO_Project();
		
		if(mCursor.moveToFirst())
		{
			mProject.setgProID(mCursor.getString(0).trim());
			mProject.setgProName(mCursor.getString(1).trim());
			mProject.setgProDescription(mCursor.getString(2).trim());
			mProject.setgProStartTime(new Time(mCursor.getLong(3)));
			mProject.setgProDeadline(new Time(mCursor.getLong(4)));
			mProject.setgProStatus(mCursor.getString(5).trim());
			mProject.setgProAvatar(mCursor.getString(6).trim());
			
			gDatabase.execSQL(sql);
		
			this.closeDatabase();
		
			return mProject;
		}
		else
		{
			this.closeDatabase();
			return null;
		}
	}
	
	
	//lay tat ca thong tin ve cac project trong database
	public Vector<DTO_Project> getAllProject() {
		
		this.openReadDatabase();
		
		Vector<DTO_Project> list = new Vector<DTO_Project>();
		
		DTO_Project mProject = new DTO_Project();
		
		String sql = "SELECT * FROM " + TABLE_NAME;
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		if(mCursor.moveToFirst())
		{
			do
			{
				mProject.setgProID(mCursor.getString(0));
				mProject.setgProName(mCursor.getString(1));
				mProject.setgProDescription(mCursor.getString(2));
				mProject.setgProStartTime(new Time(mCursor.getLong(3)));
				mProject.setgProDeadline(new Time(mCursor.getLong(4)));
				mProject.setgProStatus(mCursor.getString(5));
				mProject.setgProAvatar(mCursor.getString(6));
				
				list.add(mProject);
				
			}while(mCursor.moveToNext());
			
			this.closeDatabase();
			
			return list;
			
		}
		else {
			
			this.closeDatabase();
			
			return null;
		}
	}
}
